package com.zz.ecommerce.reportStat;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 统计分析dao
 *
 * @author
 */
@Repository
public interface ReportStatDao {

//    @Select("  SELECT A.CONSIGNOR_COUNTRY_CODE COUNTRY, A.TOTAL TOTAL, ROWNUM"
//            + " FROM (SELECT CONSIGNOR_COUNTRY_CODE, COUNT(1) TOTAL"
//            + "        FROM ORDER_HEAD A"
//            + "        WHERE A.IE_TYPE = 'I'"
//            + "        AND A.CONSIGNEE_COUNTRY_CODE = 'China'"
//            + "        AND ORDER_TIME >= ADD_MONTHS(SYSDATE,-1)"
//            + "        GROUP BY CONSIGNOR_COUNTRY_CODE"
//            + "        ORDER BY TOTAL DESC) A"
//            + " WHERE ROWNUM <= 5")

    @Select("  select countyName COUNTRY,count(1) TOTAL from (\n" +
            "select (select name_e from busi_param.S_TRADE_COUNTRY_E where code_e=t_declform_head_qdc.trade_Count and rownum=1) as countyName from t_declform_head_qdc \n" +
            "where org_id=#{orgId}  and ie_flag='I' ) where countyName is not null group by countyName")
    List<Map<String, String>> totalImportOrder(String orgId);

    @Select(" select countyName COUNTRY,count(1) TOTAL from (\n" +
            "select (select name_e from busi_param.S_TRADE_COUNTRY_E where code_e=t_declform_head_qdc.trade_Count and rownum=1) as countyName from t_declform_head_qdc" +
            " \n" +
            "where org_id=#{orgId} AND ie_flag='E' ) where countyName is not null group by countyName")
    List<Map<String, String>> totalExportOrder(String orgId);

    @Select(" SELECT A.EB_NAME NAME, A.VALUE\n" +
            "   FROM (SELECT T.EB_CODE, T.EB_NAME, SUM(T.TOTAL_PAYMENT) VALUE\n" +
            "           FROM T_ORDER_HEAD_QDC T\n" +
            "          GROUP BY T.EB_CODE, T.EB_NAME) A\n" +
            "  WHERE A.EB_NAME IS NOT NULL\n" +
            "  ORDER BY A.VALUE DESC")
    List<StsAnalysisBean> qryEntStsChartC();

    @Select(" SELECT A.EB_NAME NAME, A.VALUE\n" +
            "   FROM (SELECT T.EB_CODE, T.EB_NAME, COUNT(1) VALUE\n" +
            "           FROM T_ORDER_HEAD_QDC T\n" +
            "          GROUP BY T.EB_CODE, T.EB_NAME) A\n" +
            "  WHERE A.EB_NAME IS NOT NULL\n" +
            "  ORDER BY A.VALUE DESC")
    List<StsAnalysisBean> qryEntStsChartB();

    @Select("SELECT TO_CHAR(T.CREATE_TIME, 'YYYY-MM') NAME, COUNT(1) VALUE \n" +
            "              FROM T_DECLARE_SUBJECT_BACKUP_E T \n" +
            "             WHERE T.${colName} = '1' AND TO_CHAR(T.CREATE_TIME, 'YYYY-MM') > \n" +
            "                   TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYY-MM') \n" +
            "             GROUP BY TO_CHAR(T.CREATE_TIME, 'YYYY-MM') \n" +
            "             ORDER BY TO_CHAR(T.CREATE_TIME, 'YYYY-MM') DESC")
    List<StsAnalysisBean> qryEntStsChartE(@Param("colName") String colName);

    @Select("SELECT TO_CHAR(T.CREATE_TIME, 'YYYY-MM') NAME, COUNT(1) VALUE\n" +
            "  FROM T_GOODS_BACKUP_HEAD_INSP T\n" +
            " WHERE TO_CHAR(T.CREATE_TIME, 'YYYY-MM') >\n" +
            "       TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYY-MM')\n" +
            " GROUP BY TO_CHAR(T.CREATE_TIME, 'YYYY-MM')\n" +
            " ORDER BY TO_CHAR(T.CREATE_TIME, 'YYYY-MM') DESC")
    List<StsAnalysisBean> qryEntStsChartD();
}
